package data;

import java.sql.*;
import java.util.*;

import utils.UIDGenerator;
import domain.*;


public class JDBCUsuarioDAO implements IUsuarioDAO{
	
	private Connection conn;
	
	 public JDBCUsuarioDAO() {
        conn = ConnectionManager.getInstance().checkOut();
       
    }
	 
	 
	 public int cuentaUsuarios(int idrol){
		 PreparedStatement stmt = null;
		 ResultSet result = null;
		 int ret=0;
		 String sql = "SELECT COUNT(*) FROM usuario WHERE (rol_asignado= ?)";
		 
		 try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setInt(9, idrol);
	            result = stmt.executeQuery();
	            
	            while(result.next()){
	            	 
	            	ret=result.getInt(1);
	            }
		 } catch (SQLException e) {
	         System.out.println("Message: " + e.getMessage());
	         System.out.println("SQLState: " + e.getSQLState());
	         System.out.println("ErrorCode: " + e.getErrorCode());
	     } finally {
	         ConnectionManager.getInstance().checkIn(conn);
	         try {
	             if (result != null)
	                 result.close();
	             if (stmt != null)
	                 stmt.close();
	         } catch (SQLException e) {
	         }

	     }
	     return ret;
	     }       
	            
		 
	 
	 public String getRol(String username){
		 PreparedStatement stmt = null;
		 String rol=null;
		 ResultSet result = null;
		 String sql = "SELECT * FROM usuario WHERE (nick = ? )";
		 try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1,username);
	            result = stmt.executeQuery();
	           
	            
	           result.next();
	        	rol=new String();
	           	rol=String.valueOf(result.getInt("rol_asignado"));
	           
	    } catch (SQLException e) {
	         System.out.println("Message: " + e.getMessage());
	         System.out.println("SQLState: " + e.getSQLState());
	         System.out.println("ErrorCode: " + e.getErrorCode());
	     } finally {
	         ConnectionManager.getInstance().checkIn(conn);
	         try {
	             if (result != null)
	                 result.close();
	             if (stmt != null)
	                 stmt.close();
	         } catch (SQLException e) {
	         }

	     }
	     return rol;
	     }
	 
	 
	 public List<Usuario> selectAll(){
		 PreparedStatement stmt = null;
		 List<Usuario> searchResults=new LinkedList();
		 ResultSet result = null;
		 String sql = "SELECT * FROM usuario where estado=1";
		 
		 try {
	            stmt = conn.prepareStatement(sql);
	            result = stmt.executeQuery();
	            while(result.next()){
	            	Usuario temp = new Usuario();
	            	temp.setNick(result.getString("nick"));
	            	temp.setPass(result.getString("pass"));
	            	temp.setNombre(result.getString("nombre"));
	            	temp.setId(result.getString("OIDusuario"));
	            	searchResults.add(temp);
	            }
	           
         
     } catch (SQLException e) {
         System.out.println("Message: " + e.getMessage());
         System.out.println("SQLState: " + e.getSQLState());
         System.out.println("ErrorCode: " + e.getErrorCode());
     } finally {
         ConnectionManager.getInstance().checkIn(conn);
         try {
             if (result != null)
                 result.close();
             if (stmt != null)
                 stmt.close();
         } catch (SQLException e) {
         }

     }
     return searchResults;
     }
		 

	 public Donador selectDonador( String usuarioOID){
	
		PreparedStatement stmt = null;
		Donador receptor = null;
		ResultSet result = null;
		String sql = "SELECT * FROM usuario WHERE (OIDusuario = ?) AND rol_asignado=6";
		
		try {
            stmt = conn.prepareStatement(sql);
            stmt.setString(1, usuarioOID);
            result = stmt.executeQuery();

            result.next();
            receptor = new Donador();
            receptor.setDni(result.getString("dni"));
            receptor.setNombre(result.getString("nombre"));
            receptor.setApellidos(result.getString("apellidos"));
            receptor.setDireccion(result.getString("direccion"));
            receptor.setNick(result.getString("nick"));
            receptor.setPass(result.getString("pass"));
            
            
            
            
		  } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (result != null) {
	                    result.close();
	                }
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	        return receptor;
		}

	 public Receptor select(String usuarioOID){
			
			PreparedStatement stmt = null;
			Receptor receptor = null;
			ResultSet result = null;
			String sql = "SELECT * FROM usuario WHERE (usuarioOID = ?)";
			
			try {
	            stmt = conn.prepareStatement(sql);
	            stmt.setString(1, usuarioOID);
	            result = stmt.executeQuery();

	            result.next();
	            receptor = new Receptor();
	            receptor.setDni(result.getString("dni"));
	            receptor.setNombre(result.getString("nombre"));
	            receptor.setApellidos(result.getString("apellidos"));
	            receptor.setDireccion(result.getString("apellidos"));
	            receptor.setNick(result.getString("nick"));
	            receptor.setPass(result.getString("pass"));
	            
	            
	            
	            receptor.setEstado(result.getInt("estado"));
	            //obtener un collection?
	            receptor.setPedidosRealizados(null);
	            receptor.setProductosSemanales(result.getInt("productos"));
	            
	           
	            
	            
			  } catch (SQLException e) {
		            System.out.println("Message: " + e.getMessage());
		            System.out.println("SQLState: " + e.getSQLState());
		            System.out.println("ErrorCode: " + e.getErrorCode());
		        } finally {
		            try {
		                if (result != null) {
		                    result.close();
		                }
		                if (stmt != null) {
		                    stmt.close();
		                }
		            } catch (SQLException e) {
		            }
		        }
		        return receptor;
			}

	
	public void insertVoluntario(Voluntario vol) {
		 	String oidu = UIDGenerator.getInstance().getKey();
		 	
		 	//insert ranking
		 	
	        
	        
	        PreparedStatement stmt = null;
	        String sql = "INSERT INTO usuario (dni, nombre, apellidos, direccion,cp,poblacion,telefono,ranking,rol_asignado,OIDUsuario,nick,pass,email,estado,numInem) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
	        try {
	            stmt = conn.prepareStatement(sql);

	            stmt.setString(1, vol.getDni());
	            stmt.setString(2, vol.getNombre());
	            stmt.setString(3, vol.getApellidos());
	            stmt.setString(4, vol.getDireccion());
	            stmt.setInt(5, vol.getCp());
	            stmt.setString(6,vol.getPoblacion());
	            stmt.setInt(7, vol.getTelefono());
	            stmt.setString(8, vol.getRanking());
	            stmt.setInt(9, vol.getRolAsignado());
	            stmt.setString(10, oidu);
	            stmt.setString(11, vol.getNick());
	            stmt.setString(12, vol.getPass());
	            stmt.setString(13, vol.getEmail());	
	            stmt.setInt(14,1);
	            stmt.setString(15,null);
	            
	            stmt.executeUpdate();

	           

	        } catch (SQLException e) {
	            System.out.println("Message: " + e.getMessage());
	            System.out.println("SQLState: " + e.getSQLState());
	            System.out.println("ErrorCode: " + e.getErrorCode());
	        } finally {
	            try {
	                if (stmt != null) {
	                    stmt.close();
	                }
	            } catch (SQLException e) {
	            }
	        }
	    }
		
	
	public void insertReceptor(Receptor rec) {
		
		
		String oidu = UIDGenerator.getInstance().getKey();
	 	
	 	//insert ranking
	 	
        String oidr = UIDGenerator.getInstance().getKey();
        
        PreparedStatement stmt = null;
        String sql = "INSERT INTO usuario (dni, nombre, apellidos, direccion,cp,poblacion,telefono,ranking,rol_asignado,OIDUsuario,nick,pass,email,estado,numInem) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, rec.getDni());
            stmt.setString(2, rec.getNombre());
            stmt.setString(3, rec.getApellidos());
            stmt.setString(4, rec.getDireccion());
            stmt.setInt(5, rec.getCp());
            stmt.setString(6,rec.getPoblacion());
            stmt.setInt(7, rec.getTelefono());
            stmt.setString(8, null);
            stmt.setInt(9, 7);
            stmt.setString(10, oidu);
            stmt.setString(11, rec.getNick());
            stmt.setString(12, rec.getPass());
            stmt.setString(13, rec.getEmail());	
            stmt.setInt(14,0);
            stmt.setString(15,rec.getNumInem());
            
            stmt.executeUpdate();

           

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
    }
	
	public void insertDonador(Donador don) {
		String oidu = UIDGenerator.getInstance().getKey();
	 	
	 	//insert ranking
	 	
        String oidr = UIDGenerator.getInstance().getKey();
        
        PreparedStatement stmt = null;
        String sql = "INSERT INTO usuario (dni, nombre, apellidos, direccion,cp,poblacion,telefono,ranking,rol_asignado,OIDUsuario,nick,pass,email,estado,numInem) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
        try {
            stmt = conn.prepareStatement(sql);

            stmt.setString(1, don.getDni());
            stmt.setString(2, don.getNombre());
            stmt.setString(3, don.getApellidos());
            stmt.setString(4, don.getDireccion());
            stmt.setInt(5, don.getCp());
            stmt.setString(6,don.getPoblacion());
            stmt.setInt(7, don.getTelefono());
            stmt.setString(8, null);
            stmt.setInt(9, 6);
            stmt.setString(10, oidu);
            stmt.setString(11, don.getNick());
            stmt.setString(12, don.getPass());
            stmt.setString(13, don.getEmail());	
            stmt.setInt(14,1);
            stmt.setString(15,null);
            
            
            stmt.executeUpdate();

           

        } catch (SQLException e) {
            System.out.println("Message: " + e.getMessage());
            System.out.println("SQLState: " + e.getSQLState());
            System.out.println("ErrorCode: " + e.getErrorCode());
        } finally {
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
            }
        }
    }	
		
	


	
	
}
